<?php
//统计
namespace app\ausadmin\controller;
use app\ausadmin\controller\Common;
use think\Request;
use think\Db;
use think\Session;
use think\Cookie;
use think\Controller;
use think\Config;
use Godok\Org\Auth;

class Statisticsuser extends Common{

    //客户统计
    public function index(){

        $time = "thismonth";
        if(input('time')){
            $time =  input('time');
        }
        if($time == 'thisyear' || $time=='lastyear'){
            $day = "'%m'";
            $unit = '月';
        }else {
            $unit = '日';
            $day = "'%d'";
        }
        $kaishitime = strtotime(input('starttime'));
        $jieshutime = strtotime(input('endtime'));
        //自定义时间段统计查询
        if(!empty($kaishitime) and !empty($jieshutime)){
            //$w = " create_time >= $kaishitime and create_time <= $jieshutime ";//初始化时间段
            $where = array(
                'is_delete' 	=>	1,

            );
            $w = " create_time >= $kaishitime and create_time <= $jieshutime ";
            $day= "'%m月%d'";
            $time = '';
            $this->assign('kaishitime',input('starttime'));
            $this->assign('jieshutime',input('endtime'));
            //充值金额统计
            $Recharge = Db::name('rechargeorder')
                ->where($where)
                ->where($w)
                ->where('balance','>',0)
                ->where('pay_status',1)
                ->field("FROM_UNIXTIME(create_time,$day) as unit,sum(real_amount) as amount")
                ->group("unit")
                ->select();
            //echo Db::name('rechargeorder')->getlastsql(); var_dump($Recharge);die;
            $sumrecharge = Db::name('rechargeorder')
                ->where($where)
                ->where('balance','neq','')
                ->sum('real_amount');
            //echo $sumrecharge;die;
            //租金统计
            $leaseorder = Db::name('leaseorder')
                ->where($where)
                ->where($w)
                ->where(['status'=>2])
                ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(real_amount),0) as amount")
                ->group("unit")
                ->select();
            $sumleaseorder = Db::name('leaseorder')
                ->where($where)
                ->where($w)
                ->where(['status'=>2])
                ->sum('real_amount');
            //押金退款 
            //$re['b.refund_time'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');         
            $refund = DB::name('rechargeorder')
                ->alias('a')
                ->join("refund b",'a.oid = b.oid and a.order_no = b.order_no')
                ->where("a.create_time >= $kaishitime and a.create_time <= $jieshutime")
                ->where(['a.is_delete'=>1,'a.pay_status'=>1])
                ->where('a.refunddeposit','>',0)
                ->field("FROM_UNIXTIME(b.refund_time,$day) as unit,ifnull(sum(a.refunddeposit),0) as amount")
                ->group("unit")
                ->select();
            $sumrefund =  DB::name('rechargeorder')
                ->alias('a')
                ->join("refund b",'a.oid = b.oid and a.order_no = b.order_no')
                ->where(['a.is_delete'=>1,'a.pay_status'=>1])
                ->where("a.create_time >= $kaishitime and a.create_time <= $jieshutime")
                ->where('a.refunddeposit','>',0)
                ->sum('a.refunddeposit');
            //押金充值统计
            $depositRecharge = DB::name('rechargeorder')
                ->where($where)
                ->where($w)
                ->where('trade_no' ,'neq', '')
                ->where('deposit','>',0)
                ->where('pay_status',1)
                ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(deposit),0) as deposit")
                ->group('unit')
                ->select();
            $sumdepositRecharge = DB::name('rechargeorder')
                ->where($where)
                ->where($w)
                ->where('trade_no' ,'neq', '')
                ->where('deposit','>',0)
                ->where('pay_status',1)
                ->sum('deposit') ;
            //用户统计
            $user = DB::name('user')
                ->where($where)
                ->where($w)
                ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(distinct openid),0) as count")
                ->group('unit')
                ->select();
            $countuser = DB::name('user')
                ->where($where)
                ->where($w)
                ->count('distinct openid') ;
            //商家经营状况
            $sellers = Db::name('seller')
                -> alias('a')
                ->join("leaseorder b",'a.id = b.sid')
                ->field("sum(b.real_amount) as amount,a.name as name")
                ->where('a.is_delete',1)
                ->where("a.create_time >= $kaishitime and a.create_time <= $jieshutime")
                /*->where('a.oid',session('oid'))*/
                ->group("name")
                ->select();
            $sumsellers = Db::name('seller')
                -> alias('a')
                ->join("leaseorder b",'a.id = b.sid')
                ->where('a.is_delete',1)
                ->where("a.create_time >= $kaishitime and a.create_time <= $jieshutime")
                /*->where('a.oid',session('oid'))*/
                ->sum('b.real_amount');


        }else{//默认时间统计查询
            //充值金额统计
            $times = gettimestamp($time);
            /*$rsearch['oid'] = session('oid');*/
            $rsearch['is_delete'] = 1;
            $rsearch['payment_date'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');
            $Recharge = Db::name('rechargeorder')
                ->where($rsearch)
                ->where('balance','>',0)
                ->where('pay_status',1)
                ->field("FROM_UNIXTIME(payment_date,$day) as unit,sum(real_amount) as amount")
                ->group("unit")
                ->select();
            $sumrecharge = Db::name('rechargeorder')
                ->where($rsearch)
                ->where('pay_status',1)
                ->where('balance','>',0)
                ->sum('real_amount');
            //租金统计
            /*$wl['oid'] = session('oid');*/
            $wl['is_delete'] = 1;
            $wl['create_time'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');
            $leaseorder = Db::name('leaseorder')
                ->where($wl)
                ->where(['status'=>2])
                ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(real_amount),0) as amount")
                ->group("unit")
                ->select();
            $sumleaseorder = Db::name('leaseorder')
                ->where($wl)
                ->where(['status'=>2])
                ->sum('real_amount');
            //押金退款统计
            /* 数据查询参照
            SELECT FROM_UNIXTIME(create_time,'%d') as unit,ifnull(sum(amount),0) as amount FROM `leaseorder` WHERE `oid` = 1 AND `is_delete` = 1 AND ( `create_time` >= 1496246400 and `create_time` <= 1498838399 ) GROUP BY unit
             */

            $b = 'refund';
            /*$re['a.oid'] = session('oid');*/
            $re['a.is_delete'] = 1;
            $re['b.refund_time'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');
            //$re = "a.oid = ".session('oid')." AND a.is_delete = 1 AND b.refund_time >=".$time['starttime']." AND b.refund_time <= ".$time['endtime'];
            $refund = DB::name('rechargeorder')
                ->alias('a')
                ->join("$b b",'a.oid = b.oid and a.order_no = b.order_no')
                ->where($re)
                ->where('a.refunddeposit','>',0)
                ->where('a.pay_status',1)
                ->field("FROM_UNIXTIME(b.refund_time,$day) as unit,ifnull(sum(a.refunddeposit),0) as amount")
                ->group("unit")
                ->select();
            $sumrefund =  DB::name('rechargeorder')
                ->alias('a')
                ->join("refund b",'a.oid = b.oid and a.order_no = b.order_no')
                ->where('a.pay_status',1)
                ->where($re)
                ->where('a.refunddeposit','>',0)
                ->sum('a.refunddeposit');
            //押金充值统计
            $depositRecharge = DB::name('rechargeorder')
                ->where($wl)
                ->where('trade_no' ,'neq', '')
                ->where('deposit','>',0)
                ->where('pay_status',1)
                ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(deposit),0) as deposit")
                ->group('unit')
                ->select();
            $sumdepositRecharge = DB::name('rechargeorder')
                ->where($wl)
                ->where('trade_no' ,'neq', '')
                ->where('deposit','>',0)
                ->where('pay_status',1)
                ->sum('deposit') ;
            //用户统计
            $user = DB::name('user')
                ->where($wl)
                ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(distinct openid),0) as count")
                ->group('unit')
                ->select();
            $countuser = DB::name('user')
                ->where($wl)
                ->count('distinct openid') ;
            //商户统计
            $s = 'leaseorder';
            /*$seller['a.oid'] = session('oid');*/
            $seller['a.is_delete'] = 1;
            $seller['b.create_time'] = array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and');
            $sellers = Db::name('seller')
                -> alias('a')
                ->join("$s b",'a.id = b.sid')
                ->where($seller)
                ->field("sum(b.real_amount) as amount,a.name as name")
                ->group("name")
                ->select();
            $sumsellers = Db::name('seller')
                -> alias('a')
                ->join("leaseorder b",'a.id = b.sid')
                ->where('a.is_delete',1)
                ->where(['b.create_time'=>array(array('egt',$times['starttime']),array('elt',$times['endtime']), 'and')])
                /*->where('a.oid',session('oid'))*/
                ->sum('b.real_amount');
            //商户统计
        }

        //数据处理（跨月的一周统计排序问题）
        $RechargeArr = $leaseorderArr = $refundArr = $depositRechargeArr = $userArr = [];
        if($day == "'%m%d'")
        {
            foreach ($Recharge as $k => $v)
            {
                $RechargeArr[] = [
                    'unit'      => substr($v['unit'],2,2),
                    'amount'    => $v['amount'],
                ];
            }
            foreach ($leaseorder as $k => $v)
            {
                $leaseorderArr[] = [
                    'unit'      => substr($v['unit'],2,2),
                    'amount'    => $v['amount'],
                ];
            }
            foreach ($refund as $k => $v)
            {
                $refundArr[] = [
                    'unit'      => substr($v['unit'],2,2),
                    'amount'    => $v['amount'],
                ];
            }
            foreach ($depositRecharge as $k => $v)
            {
                $depositRechargeArr[] = [
                    'unit'      => substr($v['unit'],2,2),
                    'deposit'   => $v['deposit'],
                ];
            }
            foreach ($user as $k => $v)
            {
                $userArr[] = [
                    'unit'      => substr($v['unit'],2,2),
                    'count'     => $v['count'],
                ];
            }
        }else{
            $RechargeArr        = $Recharge;
            $leaseorderArr      = $leaseorder;
            $refundArr          = $refund;
            $depositRechargeArr = $depositRecharge;
            $userArr            = $user;
        }

        $data=array(
            'Recharge' 				=> $RechargeArr,
            'leaseorder' 			=> $leaseorderArr,
            'refund'     			=> $refundArr,
            'depositRecharge' 		=> $depositRechargeArr,
            'user'       			=> $userArr,
            'sellers'    			=> $sellers,
            'sumrecharge' 			=> $sumrecharge,
            'sumleaseorder'			=> $sumleaseorder,
            'sumrefund'				=> $sumrefund,
            'sumdepositRecharge'	=> $sumdepositRecharge,
            'countuser'				=> $countuser,
            'sumsellers'			=> $sumsellers,
        );
        $this->assign('unit', lang($unit));
        $this->assign('time',$time);
        $this->assign('data',$data);

        return $this->fetch();
    }
}


